using System;
using System.Collections.Generic;
using System.Data;
using Oracle.DataAccess.Client;
using ScrumManagementTool.db.interfaces;

namespace ScrumManagementTool.db
{
    public class DbGateway : IDbGateway
    {
        public string connectionString = "Data Source=localhost;User Id=scrummanagement;Password=123;";

        public bool ExecuteSelectForBool(string selectStatement, IDictionary<string, string> values)
        {
            var data = ExecuteSelectStatement(selectStatement, values);
            return data.Count > 0;
        }

        public IList<DataRow> ExecuteSelectStatement(string selectStatement, IDictionary<string, string> values)
        {
            IList<DataRow> data = new List<DataRow>();
            var rawData = new DataTable();

            using (var connection = new OracleConnection(connectionString))
            {
                OracleCommand command = BuildCommand(connection, selectStatement, values);
                var da = new OracleDataAdapter(command);
                da.Fill(rawData);
            }

            foreach (DataRow row in rawData.Rows)
            {
                data.Add(row);
            }

            return data;
        }

        private OracleCommand BuildCommand(OracleConnection connection, string selectStatement, IDictionary<string, string> values)
        {
            OracleCommand cmd = connection.CreateCommand();
            cmd.CommandText = selectStatement;

            cmd.Prepare();

            OracleParameter param;
            foreach (var value in values)
            {
                param = new OracleParameter();
                param.ParameterName = value.Key;
                param.Value = value.Value;

                cmd.Parameters.Add(param);
            }

            return cmd;
        }

        public void ExecuteInsertStatement(string insertStatement, IDictionary<string,string> values)
        {
            OracleConnection connection = new OracleConnection(connectionString);
            connection.Open();
            OracleCommand cmd = connection.CreateCommand();
            cmd.CommandText = insertStatement;

            cmd.Prepare();

            OracleParameter param;
            foreach (var value in values)
            {
                param = new OracleParameter();
                param.ParameterName = value.Key;
                param.Value = value.Value;

                cmd.Parameters.Add(param);
            }

            cmd.ExecuteNonQuery();
            cmd.Dispose();
            connection.Close();
        }
    }
}